package cn.dansj.controller;

import cn.dansj.annotation.logging.LogRecordParams;
import cn.dansj.common.jdbc.page.PageUtils;
import cn.dansj.common.request.annotation.http.GetMethod;
import cn.dansj.common.request.annotation.http.PostMethod;
import cn.dansj.common.request.annotation.http.RequestMappingMethod;
import cn.dansj.common.request.annotation.http.RequestParam;
import cn.dansj.common.utils.bean.CacheToken;
import cn.dansj.common.utils.bean.PageResult;
import cn.dansj.common.utils.json.DictMap;
import cn.dansj.common.utils.enums.BooleanType;
import cn.dansj.enums.GlobalEnv;
import cn.dansj.common.redis.lock.RedisLock;
import cn.dansj.common.utils.transfer.*;
import com.alibaba.druid.pool.GetConnectionTimeoutException;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import cn.dansj.common.utils.exception.AsymmetrySepCharException;
import cn.dansj.common.utils.bean.Return;
import cn.dansj.common.utils.enums.DbConnectionStatus;
import cn.dansj.service.ConnectionService;
import cn.dansj.utils.project.ProjectUtils;
import cn.dansj.common.jdbc.DBUtils;
import cn.dansj.common.redis.RedisUtils;
import com.xxl.job.entity.Connection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.data.domain.Page;
import org.springframework.data.redis.RedisConnectionFailureException;
import org.springframework.data.redis.connection.DataType;
import org.springframework.data.redis.connection.RedisConnection;
import org.springframework.data.redis.connection.RedisConnectionFactory;
import cn.dansj.common.jdbc.jdbcTemplate.JdbcTemplate;
import org.springframework.web.multipart.MultipartFile;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URLDecoder;
import java.nio.charset.StandardCharsets;
import java.sql.Timestamp;
import java.util.*;

@RequestMappingMethod("/data")
public class DataController {
    private static final Logger logger = LoggerFactory.getLogger(DataController.class);
    private final ConnectionService connectionService;
    private final RedisUtils redisUtils;

    public DataController(ConnectionService connectionService, RedisUtils redisUtils) {
        this.connectionService = connectionService;
        this.redisUtils = redisUtils;
    }

    @GetMethod(value = "/connection/getData")
    public Return<?> getConnectionData(@RequestParam("search") JSONObject search, @RequestParam int page, @RequestParam int limit) {
        String name = search.getString("name");
        String connectionInfo = search.getString("connectionInfo");
        String status = search.getString("status");
        Page<Connection> connectionPage = connectionService.search(page, limit, name, connectionInfo, status);
        return Return.T(connectionPage.getContent()).setTotalRecord(connectionPage.getTotalElements());
    }

    @GetMethod(value = "/connection/getConnection")
    public Return<?> getConnection() {
        JSONObject connectionJson = ProjectUtils.getDbLabel();
        return Return.T(connectionJson);
    }

    @PostMethod(value = "/connection/checkConnect")
    public Return<?> checkConnect(@RequestParam JSONObject postData) {
        JSONObject connection = postData.getJSONObject("connection");
        String type = postData.getString("type");

        String url = connection.getString("connection_info");
        String username = connection.getString("username");
        String password = connection.getString("password");
        String sequence = connection.getString("sequence");

        boolean updateStatus = Transformation.castToBoolean(postData.getString("updateStatus"));
        DbConnectionStatus checkStatus = DbConnectionStatus.normal;
        Connection con = null;
        if (Verification.checkNotNull(sequence)) {
            con = connectionService.getConnectionBySequence(sequence);
            if (updateStatus) {
                url = Transformation.nvl(url, con.getConnectionInfo());
                username = Transformation.nvl(username, con.getUsername());
            }
        }

        try {
            if (Verification.checkNull(password) && Verification.checkNull(sequence) && !"redis".equals(type)) {
                return Return.T(false, "新增连接信息密码不能为空");
            } else if (Verification.checkNull(password) && con != null) {
                password = con.getPassword();
            }

            if (Verification.checkNull(password) && !"redis".equals(type)) {
                return Return.T(false, "密码不能为空");
            }
            if ("redis".equals(type)) {
                try {
                    RedisConnectionFactory connectionFactory = RedisUtils.getRedisConnectionFactory(url, username, password);
                    RedisConnection connect = connectionFactory.getConnection();
                    connect.ping();
                    connect.close();
                    return Return.T(true, "连接成功");
                } catch (RedisConnectionFailureException e) {
                    checkStatus = DbConnectionStatus.abnormal;
                    return Return.T(false, e.getCause().getMessage());
                } catch (Exception e) {
                    checkStatus = DbConnectionStatus.abnormal;
                    Throwable baseException = Transformation.getBaseException(e);
                    String simpleName = baseException.getClass().getSimpleName();
                    if (simpleName.equals("CannotRetrieveClusterPartitions")) {
                        return Return.T(false, baseException.getMessage());
                    }

                    return Return.T(false, "连接失败");
                }
            } else if (url.startsWith("jdbc")) {
                try {
                    DataSource connectDB = DBUtils.createJdbcTemplate(url, username, Connection.decryptPassword(password)).getDataSource();
                    assert connectDB != null;
                    java.sql.Connection connect = connectDB.getConnection();
                    connect.close();
                    return Return.T(true, "连接成功");
                } catch (Exception e) {
                    checkStatus = DbConnectionStatus.abnormal;
                    Throwable baseException = Transformation.getBaseException(e);
                    logger.error(baseException.getMessage(), e);
                    String errMsg = baseException.getMessage();
                    if (baseException instanceof GetConnectionTimeoutException) {
                        errMsg = "连接超时";
                    } else if (baseException.getMessage().contains("Access denied for user")) {
                        errMsg = "用户名或密码错误";
                    }
                    return Return.T(false, errMsg);
                }
            } else {
                updateStatus = false;
                return Return.T(false, "未知的格式");
            }
        } finally {
            if (updateStatus && con != null) {
                con.setStatus(checkStatus);
                connectionService.save(con);
            }
        }
    }

    @PostMethod(value = "/connection/modify")
    public Return<?> modifyConnection(@RequestParam JSONObject postData) {
        JSONObject connection = postData.getJSONObject("connection");
        String type = postData.getString("type");
        String name = connection.getString("name").toLowerCase();
        String sequence = connection.getString("sequence");
        String connection_info = connection.getString("connection_info");
        String username = connection.getString("username");
        String password = connection.getString("password");
        String encryptPassword;
        if (Verification.checkNull(username) && !type.equals("redis")) return Return.T(false, "用户名不能为空");

        try {
            encryptPassword = Connection.encrypt(password);
        } catch (Exception e) {
            return Return.T(false, "密码更新失败");
        }

        try {
            Connection conName = connectionService.getConnectionByName(name);
            if (Verification.checkNotNull(sequence)) {
                if (conName != null && !conName.getSequence().equals(sequence)) {
                    return Return.T(false, "连接名称已存在,无法更新");
                }
                Connection updateConnection = connectionService.getConnectionBySequence(sequence);
                updateConnection.setUsername(username);
                updateConnection.setName(name);
                updateConnection.setConnectionInfo(connection_info);
                updateConnection.setUpdateTime(GetTime.dbTimestamp());
                if (Verification.checkNotNull(encryptPassword)) updateConnection.setPassword(encryptPassword);
                connectionService.save(updateConnection);
                return Return.T(true, "连接更新成功");
            } else {
                if (conName != null) return Return.T(false, "连接名称已存在,无法新增该连接");
                Timestamp nowTime = GetTime.dbTimestamp();
                Connection conn = DictMap.parseObject(connection.toJSONString(), Connection.class);
                conn.setName(name);
                conn.setSequence(RandomUtils.sequence());
                conn.setPassword(encryptPassword);
                conn.setDbType(postData.getString("type"));
                conn.setStatus(1);
                conn.setCreator("admin");
                conn.setCrtTime(nowTime);
                conn.setUpdateTime(nowTime);
                connectionService.save(conn);
                return Return.T(true, "连接添加成功");
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return Return.T(false, "Error");
        }
    }

    @GetMethod("/import/checkTable")
    public Return<?> checkTable(@RequestParam String dbType, @RequestParam String dbName, @RequestParam String tableName) {
        Connection connection = connectionService.getConnectionByName(dbName);
        String url = connection.getConnectionInfo();
        String username = connection.getUsername();
        String password = Connection.decryptPassword(connection.getPassword());

        JdbcTemplate jdbcTemplate = DBUtils.createJdbcTemplate(url, username, password);
        String sql;
        if ("oracle".equalsIgnoreCase(dbType)) {
            sql = "select lower(column_name) field," +
                    "lower(case when data_type in ('VARCHAR2','CHAR','VARCHAR') then data_type||'('||data_length||')' " +
                    "when data_type='NUMBER' then (case when data_precision is null then data_type " +
                    "when nvl(data_scale,0)=0 then data_type||'('||data_precision||')' " +
                    "else data_type||'('||data_precision||','||data_scale||')' end) " +
                    "else data_type end) type from user_tab_columns where table_name='" + tableName.toUpperCase() + "' order by column_id";
        } else if ("mysql".equalsIgnoreCase(dbType)) {
            sql = "show full fields from " + tableName;
        } else {
            return Return.T(false, "不支持的数据库类型");
        }

        try {
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
            JSONArray pageResult = new JSONArray();
            for (Map<String, Object> result : maps) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("name", result.get("Field").toString());
                jsonObject.put("type", result.get("Type").toString());
                pageResult.add(jsonObject);
            }
            return Return.T(pageResult);
        } catch (DataAccessException e) {
            logger.error(e.getMessage(), e);
            return Return.T(false, e.getCause().getMessage());
        }
    }

    /*
     * @sepChar 封闭符
     * @splitChar 分隔符
     * */
    @GetMethod("/import/split")
    public Return<?> split(@RequestParam String sepChar, @RequestParam String splitChar, @RequestParam String splitVal) {
        try {
            return Return.T(StringUtils.split(splitVal, splitChar, sepChar));
        } catch (AsymmetrySepCharException e) {
            return Return.T(false, "封闭符数量不对称,行分割失败");
        }
    }

    /*
     * 上传文件方式,和输入方式(import)公用UploadUtils.saveToTable
     *
     * */
    @PostMethod(value = "/upload", permission = BooleanType.TRUE)
    public Return<?> importFileData(MultipartFile file, @RequestParam JSONObject jsonObject) throws IOException {
        UploadUtils uploadUtils = new UploadUtils(jsonObject, connectionService, file);
        uploadUtils.saveToTable();
        if (uploadUtils.errorHappened()) {
            return Return.T(false, uploadUtils.getErrorIndex());
        } else {
            return Return.T(true, "导入成功");
        }
    }

    @PostMethod("/import")
    public Return<?> importInputData(@RequestParam JSONObject request) throws IOException {
        UploadUtils uploadUtils = new UploadUtils(request, connectionService, null);
        uploadUtils.saveToTable();
        if (uploadUtils.errorHappened()) {
            return Return.T(false, uploadUtils.getErrorIndex());
        } else {
            return Return.T(true, "导入成功");
        }
    }

    @GetMethod("/import/config")
    public Return<?> uploadConfig() {
        return Return.T(ProjectUtils.getSettingByRedis("upload"));
    }

    @GetMethod("/redis/keys")
    public Return<?> getKeys(@RequestParam String key, @RequestParam boolean ignoreCase, @RequestParam boolean vague) {
        return Return.T(redisUtils.keys(URLDecoder.decode(key, StandardCharsets.UTF_8), ignoreCase, vague));
    }

    @GetMethod("/redis/cache")
    public Return<?> getCache(@RequestParam String key, @RequestParam int page, @RequestParam(required = false, defaultValue = "1000") int pageSize) {
        DataType dataType;
        try {
            dataType = redisUtils.type(key);
        } catch (Exception e) {
            dataType = DataType.NONE;
        }

        long expire = redisUtils.getExpire(key);

        Object returnData;

        if (DataType.LIST.equals(dataType)) {
            returnData = redisUtils.getList(key, (page - 1) * pageSize, page * pageSize - 1);
        } else if (DataType.STRING.equals(dataType)) {
            returnData = redisUtils.getString(key);
        } else if (DataType.SET.equals(dataType)) {
            returnData = redisUtils.setMember(key);
        } else if (DataType.ZSET.equals(dataType)) {
            returnData = redisUtils.zrange(key);
        } else if (DataType.HASH.equals(dataType)) {
            Map<Object, Object> sortMap = new TreeMap<>(Comparator.comparing(Object::toString));
            sortMap.putAll(redisUtils.hgetall(key));
            returnData = sortMap;
        } else {
            returnData = "";
        }

        return Return.T(DictMap.of("data", returnData, "expire", expire, "type", dataType));
    }

    @PostMethod("/redis/delete")
    public Return<?> delete(@RequestParam String key) {
        return Return.T(redisUtils.delete(key), "result");
    }

    @PostMethod("/redis/batchDel")
    public Return<?> batchDel(@RequestParam("key") List<String> key) {
        Long delete = redisUtils.delete(key);
        return Return.T(delete > 0, String.format("成功删除%s个缓存", delete));
    }

    @GetMethod("/table/getTableColumn")
    public Return<?> getTableColumn(@RequestParam String dbSequence, @RequestParam String table, @RequestParam String tableSchema) {
        //根据连接名称查询连接信息
        Connection connection = connectionService.getConnectionBySequence(dbSequence);
        //获取jdbc url
        String url = connection.getConnectionInfo();
        //获取表名
        return Return.T(DBUtils.getTableColumns(url, connection.getUsername(), Connection.decryptPassword(connection.getPassword()), tableSchema, table));
    }

    @GetMethod("/table/search")
    public Return<?> search(@RequestParam String dbSequence, @RequestParam(required = false) String table) {
        //根据连接名称查询连接信息
        Connection connection = connectionService.getConnectionBySequence(dbSequence);
        //获取表名
        return Return.T(DBUtils.getTables(connection.getConnectionInfo(), connection.getUsername(), Connection.decryptPassword(connection.getPassword()), table));
    }

    @PostMethod("/sql")
    @LogRecordParams(controller = "dataController.runSql")
    public Return<?> sql(@RequestParam String connectionId, @RequestParam String sql, CacheToken cacheToken) {
        final String md5 = Md5Utils.md5(sql.toLowerCase().trim().replaceAll("\\s+", " "));
        // 开启新的线程记录sql执行情况
        GlobalEnv.threadPool.execute(() -> {
            try (RedisLock ignored = new RedisLock("sqlRecordLock@" + md5, 3000, 2000)) {
                final String nowTime = GetTime.stringTime();
                final String key = "sqlRecord@" + cacheToken.getSequence();
                final Map<Object, Object> sqlRecord = redisUtils.hgetall(key);
                final Object record = sqlRecord.get(md5);
                Map<String, Object> singleRecord = Verification.checkNull(record) ?
                        DictMap.of("crtTime", nowTime, "updateTime", nowTime, "execNum", 0, "ddl", DBUtils.isDDL(sql), "sql", sql) :
                        DictMap.parseObject(record);
                singleRecord.put("execNum", Integer.parseInt(singleRecord.get("execNum").toString()) + 1);
                singleRecord.put("updateTime", nowTime);
                sqlRecord.put(md5, singleRecord);
                redisUtils.hmset(key, sqlRecord);
            }
        });

        final Connection connection = connectionService.getConnectionBySequence(connectionId);
        PageUtils.startPage();
        final PageUtils pageUtils = PageUtils.getInstance(connection.getConnectionInfo(), connection.getUsername(), Connection.decryptPassword(connection.getPassword()));
        final PageResult<?> pageResult = pageUtils.getPageData(sql);
        final long totalRecords = pageResult.getTotalSize();
        final List<DictMap> metaColumnData = DBUtils.isDQL(sql) ? pageUtils.getJdbcTemplate().getMetaColumnData(sql) : null;
        return Return.T(DictMap.of("sqlResult", pageResult.getData(), "column", metaColumnData)).setTotalRecord(totalRecords).setStatus(pageResult.isStatus()).setMessage(pageResult.isStatus() ? "" : pageResult.getData().toString());
    }

    @GetMethod(value = "/sqlRecord", nologging = BooleanType.TRUE)
    public Return<?> getSqlRecord(CacheToken cacheToken) {
        final Map<Object, Object> sqlRecord = redisUtils.hgetall("sqlRecord@" + cacheToken.getSequence());
        final List<DictMap> record = DictMap.parseObject(sqlRecord.values(), new DictMap.JavaType<>() {
        });

        return Return.T(record.stream().sorted((o1, o2) -> o2.getDate("updateTime").compareTo(o1.getDate("updateTime"))).toList());
    }

    static class UploadUtils {
        private final JSONObject request;
        private final ConnectionService connectionService;
        private final MultipartFile file;
        private int lineIndex = 0;
        private boolean skipFirst = false;
        private final List<String> splitFailedIndexList = new ArrayList<>();
        private final List<String> outRangeIndexList = new ArrayList<>();
        private final List<String> emptyLineList = new ArrayList<>();
        private int maxCommit = 5000;
        private final String batchNo = RandomUtils.getUid();

        public UploadUtils(JSONObject request, ConnectionService connectionService, MultipartFile file) {
            this.request = request;
            this.connectionService = connectionService;
            this.file = file;
        }

        public void saveToTable() throws IOException {
            //数据库名称
            String dbName = request.getString("dbName");
            //封闭符
            String sepChar = request.getString("sepChar");
            //分隔符
            String splitChar = request.getString("splitChar");
            //字段展示列表
            String fieldList = request.getString("fieldList");
            //选中的字段列表
            String checkList = request.getString("checkList");
            //表名
            String tableName = request.getString("tableName");
            //上传的数据类型 file、input
            String dataType = request.getString("dataType");
            logger.info("upload id:{}, date type:{}", batchNo, dataType);
            //上传的数据内容 仅当dataType=input有值
            String data = request.getString("data");
            //是否清空表数据
            boolean truncate = request.getBoolean("isTruncate");
            logger.info("upload id:{}, truncate table:{}", batchNo, truncate);
            //是否包含表头
            boolean header = request.getBoolean("isHeader");
            //单次提交数量
            maxCommit = request.getIntValue("maxCommit") > 0 ? request.getIntValue("maxCommit") : maxCommit;
            //文件行数据切割后字段个数
            int contentFieldNum = JSONArray.parseArray(request.getString("contentField")).size();
            //文件字段列表选中的字段
            List<Object> chosenField = ArrayUtils.asList(JSONArray.parseArray(request.getString("chosenField")).toArray());
            //调整选中的字段的顺序,防止因为取消又选中导致顺序混乱
            List<String> fields = new ArrayList<>();
            for (Object field : JSONArray.parseArray(fieldList)) {
                for (Object check : JSONArray.parseArray(checkList)) {
                    String fieldName = JSONObject.parseObject(field.toString()).getString("name");
                    String checkName = ArrayUtils.asList(check.toString().split("\\(")).get(0);
                    if (checkName.equals(fieldName)) {
                        fields.add(checkName);
                    }
                }
            }
            //生成SQL的字段
            String sqlField = String.join(",", fields);
            //生成SQL的value部分的占位符
            String valueField = String.join(",", Collections.nCopies(fields.size(), "?"));
            String insertSql = String.format("insert into %s (%s) values(%s)", tableName, sqlField, valueField);
            logger.info("upload id:{}, 生成insertSql:{}", batchNo, insertSql);

            List<Object[]> dataValue = new ArrayList<>();
            List<Integer> chosenFiledIndex = new ArrayList<>();
            //如果文件分割的字段选择的数量和总数相同或者为0,则表示全部字段都导入,否则根据字段索引选择对应位置的字段
            boolean getAll;
            if (chosenField.isEmpty() || chosenField.size() == contentFieldNum) {
                getAll = true;
            } else {
                for (Object object : chosenField) {
                    chosenFiledIndex.add(Integer.parseInt(ArrayUtils.asList(((String) object).split(":")).get(0)) - 1);
                }
                getAll = false;
            }

            Connection connection = connectionService.getConnectionByName(dbName);

            JdbcTemplate jdbcTemplate = DBUtils.createJdbcTemplate(connection.getConnectionInfo(), connection.getUsername(), Connection.decryptPassword(connection.getPassword()));

            if (truncate) {
                jdbcTemplate.execute("truncate table " + tableName);
            }
            if ("file".equals(dataType)) {
                logger.info("upload id:{}, 开始解析文件内容, file size:{}", batchNo, file.getSize());
                InputStream inputStream = file.getInputStream();
                String charset = request.getString("charset");
                InputStreamReader isr = new InputStreamReader(inputStream, charset);
                BufferedReader br = new BufferedReader(isr);

                //循环读取文件内容,并进行解析
                String line;
                while ((line = br.readLine()) != null) {
                    if (header && lineIndex == 0 && !skipFirst) {
                        logger.info("upload id:{}, skip header line", batchNo);
                        skipFirst = true;
                        continue;
                    }
                    dataValue = loopSave(line, splitChar, sepChar, chosenFiledIndex, getAll, dataValue, jdbcTemplate, insertSql);
                }

                br.close();
                isr.close();
                inputStream.close();
            } else {
                logger.info("upload id:{}, 开始input内容", batchNo);
                for (String line : data.split("\n")) {
                    if (header && lineIndex == 0 && !skipFirst) {
                        skipFirst = true;
                        continue;
                    }
                    dataValue = loopSave(line, splitChar, sepChar, chosenFiledIndex, getAll, dataValue, jdbcTemplate, insertSql);
                }
            }

            //循环外处理不足maxCommit的量
            if (!dataValue.isEmpty()) {
                jdbcTemplate.batchUpdate(insertSql, dataValue);
            }
            logger.info("upload id:{}, upload上传结束", batchNo);
        }

        List<Object[]> loopSave(String line, String splitChar, String sepChar, List<Integer> chosenFiledIndex, boolean getAll, List<Object[]> dataValue, JdbcTemplate jdbcTemplate, String insertSql) {
            lineIndex += 1;
            try {
                List<String> newLine = StringUtils.split(line, splitChar, sepChar);
                if (Verification.checkNull(line)) {
                    emptyLineList.add(String.valueOf(lineIndex));
                } else {
                    if (getAll) {
                        dataValue.add(newLine.toArray());
                    } else {
                        List<String> lineTemp = new ArrayList<>();
                        boolean add = true;
                        for (int index : chosenFiledIndex) {
                            if (index < newLine.size()) {
                                lineTemp.add(newLine.get(index));
                            } else {
                                add = false;
                                outRangeIndexList.add(String.valueOf(lineIndex));
                                logger.error("upload id:{}, outRangeIndex:{}", batchNo, line);
                                break;
                            }
                        }
                        if (add) {
                            dataValue.add(lineTemp.toArray());
                        }
                    }
                }
            } catch (AsymmetrySepCharException e) {
                splitFailedIndexList.add(String.valueOf(lineIndex));
                logger.error("upload id:{}, asymmetrySepChar failed:{}", batchNo, line);
            }
            if (dataValue.size() >= maxCommit) {
                jdbcTemplate.batchUpdate(insertSql, dataValue);
                dataValue = new ArrayList<>();
            }

            return dataValue;
        }

        public boolean errorHappened() {
            return !splitFailedIndexList.isEmpty() || !outRangeIndexList.isEmpty() || !emptyLineList.isEmpty();
        }

        public String getErrorIndex() {
            String splitFailed = !splitFailedIndexList.isEmpty() ? String.join(",", splitFailedIndexList) : "无";
            String outRange = !outRangeIndexList.isEmpty() ? String.join(",", outRangeIndexList) : "无";
            String emptyLine = !emptyLineList.isEmpty() ? String.join(",", emptyLineList) : "无";
            return String.format("分割失败行:%s\n超出索引行:%s\n空白行:%s", splitFailed, outRange, emptyLine);
        }
    }
}